﻿#include "sqlthread.h"
#include "../../include/macro.h"
#include <QDebug>

SqlThread::SqlThread(const QString& db, const QString& conn)
    : database(db), connection(conn)
{
    for(int i=0;i<4;i++)
        currents.push_back(QVector<QPointF>());
    moveToThread(this);
}

SqlThread::~SqlThread()
{
    QSqlDatabase::database(connection).close();
}

void SqlThread::run()
{
    qDebug() << "sql "<<QThread::currentThreadId();
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL",connection); //连接的MYSQL的数据库驱动
    db.setHostName("localhost");         //主机名
    db.setPort(3306);                    //端口
    db.setDatabaseName(database);          //数据库名
    db.setUserName(TO_STR(DB_USERNAME));              //用户名
    db.setPassword("");            //密码
    db.open();

    if(!db.open())
    {
        qDebug()<<"mysql: connection '"<<connection <<"' to " << database <<" error"<<db.lastError().text();
        return;
    }
    else
    {
         qDebug()<<"mysql: connection '"<<connection <<"' to " << database << " OK";
    }
    exec();
}

void SqlThread::slt_addInjector(const InjectorData& data)
{
    QString str = QString("insert into injector.info (id,ip,name,pos,status,battery,"
                          "oil,inj_p,com_p,inj_v,max_c,min_v) "
                          "values(null,'%1','%2','%3',0,100,100,'%4','%5','%6','%7','%8');")
            .arg(data.ip).arg(data.name).arg(data.position)
            .arg(data.settings.inject_period)
            .arg(data.settings.comm_period)
            .arg(data.settings.inject_volume)
            .arg(data.settings.max_current)
            .arg(data.settings.min_volt);
    execute(str);
    get_lastId();
}

void SqlThread::slt_deleteInjector(uint id)
{
    QString str = QString("DELETE FROM injector.info WHERE id = %1;").arg(id);
    execute(str);
}

void SqlThread::slt_updateInjectorSetting(const InjectorSetting& setting, uint id)
{
    QString str = QString("UPDATE injector.info SET inj_p = %1, com_p = %2, inj_v = %3,"
                          " max_c = %4, min_v = %5 WHERE id = %6")
            .arg(setting.inject_period).arg(setting.comm_period).arg(setting.inject_volume)
            .arg(setting.max_current).arg(setting.min_volt).arg(id);
    execute(str);
    qDebug() << "inj update setting";
}

void SqlThread::slt_updateInjectorInfo(const QString& name, const QString& pos, uint id)
{
    QString str = QString("UPDATE injector.info SET name = '%1', pos = '%2' WHERE id = %3")
            .arg(name).arg(pos).arg(id);
    execute(str);
    qDebug() << "inj update info";
}

void SqlThread::slt_updateInjectorStatus(const SerialPack& pack, uint id)
{
    QString str = QString("UPDATE injector.info SET status = %1 ,battery = %2, oil = %3 , inj_p =%4, com_p=%5, inj_v =%6 WHERE id = %7;")
            .arg(pack.status).arg(pack.battery).arg(pack.oil).arg(pack.settings.inject_period)
            .arg(pack.settings.comm_period).arg(pack.settings.inject_volume).arg(id);
    execute(str);
    qDebug() << "inj update status";
}

void SqlThread::slt_addDTU(const DTUData& data)
{
    QString str = QString("insert into dtu.info (id,ip,name,pos) values(null, %1,'%2','%3');")
            .arg(data.ip)
            .arg(data.name)
            .arg(data.position);
    execute(str);
    uint id = get_lastId();
    //create table
    QString str2 = QString("CREATE TABLE `dtu`.`dtu%1` "
                           "(`time` DATETIME(3) NULL,"
                           "`r1` FLOAT NULL,`r2` FLOAT NULL,"
                           "`r3` FLOAT NULL,"
                           "`r4` FLOAT NULL);").arg(id);
    execute(str2);
}

void SqlThread::slt_deleteDTU(uint id)
{
    QString str = QString("DELETE FROM dtu.info WHERE id = %1;DROP TABLE `dtu`.`dtu%2`;")
            .arg(id).arg(id);
    execute(str);
}

void SqlThread::slt_updateDTU(const DTUData& data)
{
    QString str = QString("UPDATE dtu.info SET name = '%1', pos = '%2' WHERE id = %3")
            .arg(data.name).arg(data.position).arg(data.id);
    execute(str);
    qDebug() << "dtu update";
}

uint SqlThread::get_lastId()
{
    QString str("select LAST_INSERT_ID();");
    QSqlQuery query(QSqlDatabase::database(connection));
    query.exec(str);
    uint id;
    while(query.next())
    {
        id = query.value(0).toUInt();
        emit sig_lastId(id);
    }
    return id;
}

void SqlThread::slt_uploadCurrent(uint id, const QList<int>& data)
{
    /*
    clock_t begin,end;
    begin = clock();*/
    QString str = QString("insert into dtu.dtu%1 (time,r1,r2,r3,r4) values ").arg(id);
    for(int i=0;i<4;i++)
        str += QString("(now(3), '%1', '%2', '%3', '%4');")
                .arg(data[0]).arg(data[1]).arg(data[2]).arg(data[3]);
    /*
    for(int i=0;i<40;i+=4)
        str += QString("(now(3), '%1', '%2', '%3', '%4'),")
            .arg(data[i]).arg(data[i+1]).arg(data[i+2]).arg(data[i+3]);
    str.replace(str.length()-1,1,";");
    */
    execute(str);
    /*
    end = clock();
    qDebug() << end - begin<<endl;
*/
}

void SqlThread::execute(const QString& str)
{
    QSqlQuery query(QSqlDatabase::database(connection));
    query.exec(str);
}

void SqlThread::slt_searchCurrent(const QDateTime& start,const QDateTime& end, uint id)
{
    QString str = QString("select time, r1, r2, r3, r4 from dtu.dtu%1 where "
                          "time > '%2' and time < '%3';")
            .arg(id)
            .arg(start.toString("yyyy-MM-dd hh:mm:ss"))
            .arg(end.toString("yyyy-MM-dd hh:mm:ss"));
    QSqlQuery query(QSqlDatabase::database(connection));
    query.exec(str);
    qDebug() << str;
    for(int i=0;i<4;i++)
        currents[i].clear();
    uint64_t x = 0;
    while (query.next())
    {
        //qDebug() << query.value(1).toDateTime().toString("hh:mm:ss.zzz");
        for(int i=0;i<4;i++)
            currents[i].push_back(QPointF(x, query.value(i+1).toInt()));
        x++;
    }
    emit sig_searchCurrent(currents);
}
